//
//  DBTools.m
//  owner-leisure
//
//  Created by lanou on 16/4/27.
//  Copyright © 2016年 吴肖刚. All rights reserved.
//

#import "DBTools.h"
#import "FMDatabase.h"

// 表名
#define kTableName @"Collection"


@interface DBTools ()

@property (nonatomic, strong) FMDatabase *dataBase;

@end

@implementation DBTools

+(instancetype)sharedTools
{
    static DBTools *dbTools = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dbTools = [[DBTools alloc]init];
    });
    return dbTools;
}

-(instancetype)init
{
    self = [super init];
    if (self) {
        // 可以给nil，如果给nil的话会自动创建一个临时数据库，程序退出会自动删除，一般我们会给一个文件路径
        NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
        path = [path stringByAppendingString:@"/myDB.sqlite"];
//        path = [path stringByAppendingPathExtension:@"/myDB.sqlite"];
        NSLog(@"%@", path);
        
        self.dataBase = [FMDatabase databaseWithPath:path];
        
        [self createTable];
    }
    return self;
}

// 创建表
-(void)createTable
{
    if ([self.dataBase open]) { // 打开数据库
        NSLog(@"数据库打开成功");
        // 创建sql语句
        NSString *sql = [NSString stringWithFormat:@"create table %@(ctime text,fromWhere text,picUrl text,title text,url text primary key)", kTableName];
        // 执行sql语句
        BOOL result = [self.dataBase executeUpdate:sql];
        if (result) {
            NSLog(@"创建成功");
            
        } else {
            NSLog(@"创建失败/已经存在");
        }
    } else {
        NSLog(@"数据库打开失败");
    }    
}

// 添加数据
-(BOOL)addReadCollectionWithModel:(NewsModel *)model
{
    // sql语句
    NSString *sql = [NSString stringWithFormat:@"insert into %@(ctime,fromWhere,picUrl,title,url) values(?,?,?,?,?)", kTableName];
    // 执行语句
    BOOL isOK = [self.dataBase executeUpdate:sql, model.ctime, model.fromWhere, model.picUrl, model.title, model.url];
    return isOK;
}

// 删除数据
-(BOOL)deleteReadCollectionWithModel:(NewsModel *)model
{
    // sql语句
    NSString *sql = [NSString stringWithFormat:@"delete from %@ where url = ?", kTableName];
    
    BOOL isOK = [self.dataBase executeUpdate:sql, model.url];
    
    return isOK;
}

/*
 ctime;
 fromWhere;
 picUrl;
 title;
 url;
 */
// 查询所有数据
-(NSMutableArray<NewsModel *> *)selectReadCollection
{
    NSMutableArray *arrays = @[].mutableCopy;
    // sql语句
    NSString *sql = [NSString stringWithFormat:@"select * from %@", kTableName];
    // 执行查询语句后会返回一个结果集合
    FMResultSet *set = [self.dataBase executeQuery:sql];
    // 循环取出所有数据
    while ([set next]) {
        // 循环一次只建一个model
        NewsModel *model = [[NewsModel alloc]init];
        model.ctime = [set stringForColumn:@"ctime"];
        model.fromWhere = [set stringForColumn:@"fromWhere"];
        model.picUrl = [set stringForColumn:@"picUrl"];
        model.title = [set stringForColumn:@"title"];
        model.url = [set stringForColumn:@"url"];
        // 把model存入数组中
        [arrays addObject:model];
    }
    return arrays;
}

//// 根据ID查找
-(NewsModel *)selectReadCollectionWithURL:(NSString *)URL
{    
    NewsModel *model = nil;
    // sql语句
    NSString *sql = [NSString stringWithFormat:@"select * from %@ where url = ? ", kTableName];
    
    // executeQuery 会查询数据放在set中
    FMResultSet *set = [self.dataBase executeQuery:sql, URL];
    // model属性赋值,只有查询到数据才把model初始化
    while ([set next]) {
        model = [[NewsModel alloc]init];
        model.ctime = [set stringForColumn:@"ctime"];
        model.fromWhere = [set stringForColumn:@"fromWhere"];
        model.picUrl = [set stringForColumn:@"picUrl"];
        model.title = [set stringForColumn:@"title"];
        model.url = [set stringForColumn:@"url"];
    }
    return  model;
}

// 删除所有
-(BOOL)deleteAllReadCollection
{
    NSString *sql = [NSString stringWithFormat:@"delete from %@", kTableName];
    BOOL isOK = [self.dataBase executeUpdate:sql];
    return isOK;
}

@end
